package com.wanqh.reptitle.utils;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;

public class ExcelUtil {

    public static synchronized void appendToExcel(String sheetName, String[][] datas, String filePath) throws Exception{
        FileInputStream fs = new FileInputStream(filePath);
//        POIFSFileSystem ps = new POIFSFileSystem(fs); //使用POI提供的方法得到excel的信息
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sheet = wb.getSheet(sheetName); //获取到工作表，因为一个excel可能有多个工作表
        XSSFRow row = sheet.getRow(0);
        int hang = 0;
        if ("".equals(row) || row == null) {
            hang = 0;
        } else {
            hang = sheet.getLastRowNum();
            hang = hang + 1;
        }
        //分别得到最后一行的行号，和一条记录的最后一个单元格
        FileOutputStream out = new FileOutputStream(filePath); //向d://test.xls中写数据
        for (int i = 0; i < datas.length; i++) {
            row = sheet.createRow((short) (hang + i)); //在现有行号后追加数据
            for (int j = 0; j < datas[i].length; j++) {
                row.createCell(j).setCellValue(datas[i][j]);
            }
        }
        out.flush();
        wb.write(out);
        out.close();
    }

    public static void initExcel(String sheetName, String[] header, String filePath) throws Exception {
        FileInputStream fs = new FileInputStream(filePath);
//        POIFSFileSystem ps = new POIFSFileSystem(fs); //使用POI提供的方法得到excel的信息
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        int sheetIndex = wb.getSheetIndex(sheetName);
        if(sheetIndex >= 0) {
            wb.removeSheetAt(sheetIndex);
        }
        XSSFSheet sheet = wb.createSheet(sheetName);
//        XSSFSheet sheet = wb.getSheet(sheetName); //获取到工作表，因为一个excel可能有多个工作表
        XSSFRow row =  sheet.createRow(0);
        //分别得到最后一行的行号，和一条记录的最后一个单元格
        FileOutputStream out = new FileOutputStream(filePath); //向d://test.xls中写数据
        for (int i = 0; i < header.length; i++) {
            row.createCell(i).setCellValue(header[i]);
        }
        out.flush();
        wb.write(out);
        out.close();
    }
}
